<?php

/**
 * This is a singleton class that interacts with the database
 *
 * @author Tony
 */
include_once '../users/User.php';
include_once '../config/FileTypes.php';
include_once 'Rules.php';

class DataHandler {
  const CONFIG_PATH = '../config/';
  const AY_TABLE = 'aydata';
  const OTS_TABLE = 'otsdata';
  
  private $ay_rules;
  private $ots_rules;
  private $institution_rules;
  
  private static $instance;
  private $con;

  private function __construct() {
    $con = mysql_connect("localhost", "ttony21_Tony", "cse308");
    if (!$con) {
      die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("ttony21_GARS", $con);
    //$this->ResetDatabase();
    $this->ay_rules = new Rules(self::CONFIG_PATH . 'ay-map.txt');
    $this->ots_rules = new Rules(self::CONFIG_PATH . 'ots-map.txt');
    $this->institution_rules = new Rules(self::CONFIG_PATH . 'institution-map.txt');
    
  }

  public static function singleton() {
    if (!isset(self::$instance)) {
      $className = __CLASS__;
      self::$instance = new $className;
    }
    return self::$instance;
  }

  public function __clone() {
    trigger_error('Clone is not allowed.', E_USER_ERROR);
  }

  public function __wakeup() {
    trigger_error('Unserializing is not allowed.', E_USER_ERROR);
  }
  
  public function enterReview($rating, $review, $reviewer, $applicant) {
    $sql = "INSERT INTO reviews (reviewer, applicant, rating, review)
            VALUES('$reviewer', '$applicant', '$rating', '$review');";
    /*last_edited DATETIME NOT NULL,
	reviewer varchar(255) NOT NULL UNIQUE,
	applicant varchar(255) NOT NULL UNIQUE PRIMARY KEY,
	FOREIGN KEY (reviewer) REFERENCES users(email_address),
	FOREIGN KEY (applicant) REFERENCES aydata(email_address),
	rating FLOAT(2,1),
	review TEXT*/
    return mysql_query($sql);
  }
  /**
   * Writes a set of records to a database table
   * @param array $records
   * @param type $file_type
   */
  public function writeToDB($records, $file_type) {
    try {
      $table_name = $this->getTable($file_type);
      $rules = $this->getRules($file_type);
    } catch (Exception $e) {
      echo $e->getMessage();
    }
    //Add records to database    
    foreach($records as $record) {
      $sql = "INSERT INTO $table_name (";
      foreach($rules as $rule) {
        $col = $rule->to;
        if (strcmp($col, 'ignore') != 0) {
          $sql .= "$col,";
        }
      }
      $sql = rtrim($sql, ',');
      $sql .= ') VALUES (';
      $i = 0;
      foreach($record as $data) {
        $col = $rules[$i++]->to;
        if (strcmp($col, 'ignore') != 0) {
          $sql .= "'" . $data . "',";
        }
      }
      $sql = rtrim($sql, ',');
      $sql .= ");";
      mysql_query($sql);
    }
  }

  /**
   * Authenticates user credentials
   * @param string $name
   * @param string $password
   * @return bool 
   */
  public function authenticate($name, $password) {
    $sql = "SELECT * 
            FROM users 
            WHERE username = '$name' 
            AND password = '" . md5($password) . "'";
    $result = mysql_query($sql);
    if ($row = mysql_fetch_array($result)) {
      //Successful login
      $username = $row['username'];
      $num_assigned_reviews = $row['num_assigned_reviews'];
      $workload = $row['workload'];
      $email = $row['email'];
      $countries = $row['countries'];
      $fields = $row['fields'];
      $role = $row['role'];
      $available = $row['available'];
      $active_user = new User($num_assigned_reviews, $workload, $username, $email, 
          $countries, $fields, $role, $available);
      //Set session vars
      $_SESSION['active_user'] = $active_user;
      return true;
    } else {
      //Failed login
      echo '<label class = "error">Invalid username or password</label>';
      return false;
    }
  }

  /**
   * Prints a table from the database
   * @param enum $file_type
   */
  public function printTable($file_type) {
    //Get table name and rules
    try {
      $table_name = $this->getTable($file_type);
      $rules = $this->getRules($file_type);
    } catch (Exception $e) {
      echo $e->getMessage();
    }
    //Get data
    $sql = "SELECT * FROM $table_name ORDER BY email_address";
    $result = mysql_query($sql);
    if (mysql_num_rows($result) == 0 ) {
      throw new Exception("No data");
    }
    //Read fields and columns
    $fields = array();
    $cols = array();
    foreach($rules as $rule) {
      $field_name = $rule->from;
      $col_name = $rule->to;
      if (strcmp($col_name, 'ignore') != 0) {
        array_push($fields, $field_name);
        array_push($cols, $col_name);
      }
    }
    //Print fields
    echo "<table class = 'appTable' id = '$table_name'>
          <thead>
          <tr>";
    foreach($fields as $field_name) {
      echo "<th scope = 'col'>$field_name</th>";
    }
    echo "</tr>
          </thead><tfoot></tfoot>";
    //Print records
    echo '<tbody>';
    while ($row = mysql_fetch_array($result)) {
      $email = $row['email_address'];
      echo "<tr class = 'unselected' title = '$email'>";
      foreach ($cols as $col_name) {
        $data = $row[$col_name];
        $data = trim($data, "\"");
        echo "<td>" . $data . "</td>";
      }
      echo "</tr>";
    }
    echo '</tbody></table>';
  }
  public function getEmailList() {
    $email_list = array();
    $email_list = $email_list 
            + $this->getEmails(self::AY_TABLE) 
            + $this->getEmails(self::OTS_TABLE);
    if (array_count_values($email_list) == 0) {
      return FALSE;
    }
    return $email_list;
  }
  private function getEmails($table) {
    $emails = array();
    $sql = "SELECT * FROM $table ORDER BY email_address";
    $result = mysql_query($sql);
    while ($row = mysql_fetch_array($result)) {
      $email = $row['email_address'];
      array_push($emails, $email);
    }
    return $emails;
  }
  /**
   * Adds user to database
   */
  private function addUser($name, $password, $role, $email_address) {
    $password = md5($password);
    $sql = "INSERT 
            INTO users (username, password, role, email_address) 
            VALUES ('$name', '$password', '$role', '$email_address')";
    mysql_query($sql);
    return $sql;
  }
  /**
   * Gets database table based on file type
   * @param type $file_type 
   */
  private function getTable($file_type) {
    if ($file_type == FileTypes::AY) {
      return self::AY_TABLE;
    } else if ($file_type == FileTypes::OTS) {
      return self::OTS_TABLE;
    } else {
      throw new Exception($file_type . ": Invalid File Type");
    }
  }
  /**
   * Gets rules map based on file type
   * @param type $file_type 
   */
  private function getRules($file_type) {
    if ($file_type == FileTypes::AY) {
      return $this->ay_rules->getRules();
    } else if ($file_type == FileTypes::OTS) {
      return $this->ots_rules->getRules();
    } else {
      throw new Exception($file_type . ": Invalid File Type");
    }
  }
  /**
   * Recreates all database tables
   */
  private function ResetDatabase() {
    mysql_query("DROP TABLE aydata");
    mysql_query("DROP TABLE otsdata");
    mysql_query("DROP TABLE reviews");
    mysql_query("DROP TABLE users");
    mysql_query("DROP TABLE decisions");
    $sql = "CREATE TABLE aydata (
	submission_date	varchar(255),
	ID	varchar(255),
	last_name	varchar(255),
	first_name	varchar(255),
	middle_name	varchar(255),
	birth_month	varchar(255),
	birth_day	varchar(255),
	birth_year	varchar(255),
	gender	varchar(255),
	ethnicity	varchar(255),
	race	varchar(255),
	citizenship	varchar(255),
	permanent_resident	varchar(255),
	phone	varchar(255),
	email_address	varchar(255) NOT NULL UNIQUE PRIMARY KEY,
	specialization	varchar(255),
	GRE_V	varchar(255),
	GRE_V_pctile	varchar(255),
	GRE_Q	varchar(255),
	GRE_Q_pctile	varchar(255),
	GRE_A	varchar(255),
	GRE_A_pctile	varchar(255),
	GRE_subj_name	varchar(255),
	GRE_subj_score	varchar(255),
	TOEFL	varchar(255),
	tse	varchar(255),
	IELTS	varchar(255),
	toefl_ibt_total_score	varchar(255),
	toefl_ibt_reading	varchar(255),
	toefl_ibt_speaking	varchar(255),
	toefl_ibt_writing	varchar(255),
	toefl_ibt_listening	varchar(255),
	TOEFL_internet	varchar(255),
	research_area	varchar(255),
	research_topics	varchar(255),
	ug_rank varchar(255),
	ug_out_of varchar(255),
	grad_rank	varchar(255),
	grad_out_of	varchar(255),
	theory_course_title	varchar(255),
	theory_gradorundergrad	varchar(255),
	theory_instructor	varchar(255),
	theory_years_taken	varchar(255),
	theory_scale	varchar(255),
	theory_grade	varchar(255),
	theory_SBU_equiv	varchar(255),
	algorithm_course_title	varchar(255),
	algorithm_gradorundergrad	varchar(255),
	algorithm_instructor	varchar(255),
	algorithm_years_taken	varchar(255),
	algorithm_scale	varchar(255),
	algorithm_grade	varchar(255),
	algorithm_SBU_equiv	varchar(255),
	prog_lang_course_title	varchar(255),
	prog_lang_gradorundergrad	varchar(255),
	prog_lang_instructor	varchar(255),
	prog_lang_years_taken	varchar(255),
	prog_lang_scale	varchar(255),
	prog_lang_grade	varchar(255),
	prog_lang_SBU_equiv	varchar(255),
	os_course_title	varchar(255),
	os_gradorundergrad	varchar(255),
	os_instructor	varchar(255),
	os_years_taken	varchar(255),
	os_scale	varchar(255),
	os_grade	varchar(255),
	os_SBU_equiv	varchar(255),
	ug_inst	varchar(255),
	ug_GPA	varchar(255),
	ug_scale	varchar(255),
	ug_GPA1	varchar(255),
	ug_GPA2	varchar(255),
	ug_GPA3	varchar(255),
	ug_GPA4	varchar(255),
	ug_GPA5	varchar(255),
	grad_inst	varchar(255),
	grad_GPA	varchar(255),
	grad_scale	varchar(255),
	grad_GPA1	varchar(255),
	grad_GPA2	varchar(255)
        );";
    mysql_query($sql);
    $sql = "create table decisions (
	last_edited DATETIME NOT NULL,
	chair varchar(255) NOT NULL UNIQUE,
	applicant varchar(255) NOT NULL UNIQUE PRIMARY KEY,
	FOREIGN KEY (chair) REFERENCES users(email_address),
	FOREIGN KEY (applicant) REFERENCES aydata(email_address),
	rating FLOAT(2,1) DEFAULT -1.0,
	comments TEXT
        );";
    mysql_query($sql);
    $sql = "Create table otsdata (
	last_name varchar(255),
	first_name varchar(255),
	email_address varchar(255) NOT NULL UNIQUE PRIMARY KEY,
	citizenship varchar(255),
	birth_month varchar(255),
	birth_day varchar(255),
	birth_year varchar(255),
	gender varchar(255),
	ofcl_GRE_V varchar(255),
	ofcl_GRE_V_pctile varchar(255),
	ofcl_GRE_Q varchar(255),
	ofcl_GRE_Q_pctile varchar(255),
	ofcl_GRE_A varchar(255),
	ofcl_GRE_A_pctile varchar(255),
	ofcl_GRE_subj varchar(255),
	ofcl_GRE_subj_pctile varchar(255),
	ofcl_GRE_subj_name varchar(255),
	ofcl_TOEFL_total varchar(255),
	ofcl_TOEFL_listen varchar(255),
	ofcl_TOEFL_read varchar(255),
	ofcl_TOEFL_speak varchar(255),
	ofcl_TOEFL_write varchar(255)
        );";
    mysql_query($sql);
    $sql = "create table reviews (
	last_edited DATETIME NOT NULL,
	reviewer varchar(255) NOT NULL UNIQUE,
	applicant varchar(255) NOT NULL UNIQUE PRIMARY KEY,
	FOREIGN KEY (reviewer) REFERENCES users(email_address),
	FOREIGN KEY (applicant) REFERENCES aydata(email_address),
	rating FLOAT(2,1),
	review TEXT
        );";
    mysql_query($sql);
    $sql = "CREATE TABLE users (
	username varchar(255) NOT NULL UNIQUE PRIMARY KEY,
	password varchar(255) NOT NULL,
	role ENUM('CHAIR', 'FACULTY', 'STAFF') NOT NULL,
	email_address varchar(255)  NOT NULL,
	countries varchar(255),
	research_area varchar(255),
	numAssignedReviews INT UNSIGNED,
	boolean varchar(255),
	workload FLOAT UNSIGNED
        );";
    mysql_query($sql);
    $this->addUser('Chair', 'password', 'CHAIR', 'chair.smith@company.org');
    $this->addUser('Bob', '12345', 'FACULTY', 'bob@fakey.com');
  }
}

?>
